package com.meeting.y.controller;

import com.jfinal.core.ActionKey;
import com.jfinal.core.Controller;
import com.jfinal.kit.Ret;
import com.jfinal.log.Log;
import com.jfinal.render.Render;
import com.jfinal.upload.UploadFile;
import com.meeting.y.model.AuthUser;
import com.meeting.y.model.MeetingsMeeting;
import com.meeting.y.model.MeetingsMeetingattendee;
import com.meeting.y.model.MeetingsRoom;
import com.meeting.y.util.NumberTool;
import com.meeting.y.util.StringUtil;
import com.meeting.y.util.TimeUtil;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Time;
import java.text.NumberFormat;
import java.util.*;

public class UploadClassesController extends Controller {

    private static final Logger logger = LoggerFactory.getLogger(UploadClassesController.class);

    /*
    教室	老师	"       星期
            时间"	星期一	星期二	星期三	星期四	星期五	星期六	星期天
     */
    private static final String[] Class_PROPERTY =
            {"教室", "老师", "星期\n  时间","星期一", "星期二", "星期三", "星期四", "星期五", "星期六", "星期天"};

    public static Map<String,Integer> _teacherNameid = new HashMap<>();
    public static Map<String,Integer> _studentNameid = new HashMap<>();
    public static Map<String,Integer> _roomNameid = new HashMap<>();
    public static Map<Integer,Integer> _teacherid_roomid = new HashMap<>();

    @ActionKey("/api/meeting/upload/cfg")
    public void loadimpcfg(){
        Map map = new HashMap();
        String now = TimeUtil.getCurrentDate("yyyy-MM-dd");
        String lastSessionDate = TimeUtil.formatDate(TimeUtil.getLastDateOfSeason(new Date()),"yyyy-MM-dd");
        map.put("nowdate",now);
        map.put("fromcutdate","2019-11-25");
        map.put("tocutdate","2019-11-18");
        map.put("endcopydate",lastSessionDate);
        renderJson(Ret.ok("code",0).set("message","返回成功").set("data",map));
    }

    public void copyToClass(Date fromdate,Date todate){
        List<MeetingsMeetingattendee> list = MeetingsMeetingattendee.dao.find(
                "select * from meetings_meetingattendee where meeting_id in ( select id from meetings_meeting  where delete_status = 0 and date=?) and delete_status = 0",fromdate
        );
        Map<Integer,List<MeetingsMeetingattendee>> idatt = new HashMap<>();
        for(MeetingsMeetingattendee m:list){
            List<MeetingsMeetingattendee> sub = idatt.get(m.getMeetingId());
            if(sub == null){
                sub = new ArrayList<>();
                idatt.put(m.getMeetingId(),sub);
            }
            sub.add(m);
        }
        List<MeetingsMeeting> meetingsMeetings = MeetingsMeeting.dao.find("select * from meetings_meeting  where delete_status = 0 and date=?",fromdate);
        for(MeetingsMeeting meetingsMeeting:meetingsMeetings){
            Integer id= meetingsMeeting.getId();
            meetingsMeeting.setDate(todate);
            meetingsMeeting.setId(null);
            meetingsMeeting.setDeleteStatus(false);
            meetingsMeeting.save();
            List<MeetingsMeetingattendee> _list= idatt.get(id);
            for(MeetingsMeetingattendee m:_list){
                m.setId(null);
                m.setMeetingId(meetingsMeeting.getId());
                m.setDeleteStatus(false);
                m.save();
            }
        }

    }

    public void copyClass(Date fromdate,Date todate,String type){
        if("cut".equals(type)){
            //直接update 日期
            MeetingsMeeting.dao.cutAllMeetings(fromdate,todate);
        }else if("copy".equals(type)){
            //查询，然后复制，可能有重复
            copyToClass(fromdate,todate);

        }else if("cover".equals(type)){
            //先删除 to，在复制
            MeetingsMeeting.dao.delMeetingsmeetingByDate(todate);
            copyToClass(fromdate,todate);
        }else if("delete".equals(type)){
            //删除
            Calendar calendar = Calendar.getInstance();
            for(;fromdate.before(todate);){
                MeetingsMeeting.dao.delMeetingsmeetingByDate(fromdate);
                calendar.setTime(fromdate);
                calendar.add(Calendar.DAY_OF_YEAR,1);
                fromdate = calendar.getTime();
            }
            MeetingsMeeting.dao.delMeetingsmeetingByDate(todate);
        }
    }



    @ActionKey("/api/meeting/upload/classdeal")
    public void classDeal(){
        Date fromcutdate = getParaToDate("fromcutdate");
        Date tocutdate = getParaToDate("tocutdate");
        int accountIndex = getParaToInt("accountIndex");
        List<String> types = Arrays.asList(new String[]{"","copy","delete","cut"});
        String type = types.get(accountIndex);
        this.copyClass(fromcutdate,tocutdate,type);
        renderJson(Ret.ok("code",0).set("message","返回成功"));

    }


    @ActionKey("/api/meeting/upload")
    public void uploadClasses(){

        UploadFile ufile = getFile();
        if (ufile == null) {
            renderJson(Ret.fail().set("message","无法找到文件"));
            return;
        }
        File file = ufile.getFile();

        _teacherNameid = new HashMap<>();
        _studentNameid = new HashMap<>();
        _teacherid_roomid = new HashMap<>();
        _roomNameid = new HashMap<>();
        initUser();
//        String _filepath = "C:\\work\\git\\one-JFinal-vue-element-admin\\";
//        String _filename = "1_2019年9-10月排课表9.23(2)(1).xlsx";
//        File file = new File(_filepath+_filename);
        try {
            /*nowdate:'2019-11-14',
            endcopydate:'2019-11-14',
            fromcutdate:'2019-11-14',
            tocutdate:'2019-11-14',
            covered:true,
            accounts: ["-选择-","覆盖", "复制", "删除","移动"],
            accountIndex: 0
            */
            Date nowdate = getParaToDate("nowdate");
            Date endcopydate = getParaToDate("endcopydate");
            boolean covered = getParaToBoolean("covered");
            String type = covered ?"cover":"copy";

            FileInputStream _in = new FileInputStream(file);
            Workbook workbook = UploadClassesController.parse(_in,"xlsx");
            Sheet sheet = workbook.getSheetAt(0);
            /*从B2开始读
              check老师，如果没有新增老师，返回id，map缓存
              C2 是 时间，如果课程里面有\n,可能是时间/备注，注意时间
              D2 - J2 是星期，每周的课程
                + 号表示是 分别两个小朋友
                特殊名词：秋季班下半节 秋季班上半节 秋季班全 清洁消毒
                            陪读 幼小衔接班
                需要识别：老师，时间，小朋友，课程类型，其他均为备注

                //2019年10月21日18:47:35 第一个版本问题
                    1.auth_user 表数据有重复，学生，老师
                    2.auth_user数据，有异常数据
                    3.后续还要处理room的数据
             */

            //必须表头字段，动态表头，允许前三行任一行
            Integer[] headNums=getHeadNums(Class_PROPERTY,sheet);
            if(headNums == null){
                renderJson(Ret.fail().set("message","表头不满足,请使用模板"));
                return;
            }
            Integer userid = 1;
            int rowNum = sheet.getPhysicalNumberOfRows();

            Calendar calendar = Calendar.getInstance();
            Date beginSave = TimeUtil.setTimeZone(TimeUtil.getAroundWeekFirst(1));
            Date _date = beginSave;

            for (int rowIndex = 1; rowIndex < rowNum; rowIndex++) {
                try {
                    boolean flag = true;
                    int impFlag = 0;//导入标识，0表示成功
                    //信息验证
                    _date = beginSave;
                    calendar.setTime(beginSave);
                    Log.getLog(this.getClass()).debug("------rowIndex"+rowIndex+",beginSave"+TimeUtil.formatDate(beginSave,"yyyy-MM-dd"));
                    StringBuffer message = new StringBuffer();
                    Row row = sheet.getRow(rowIndex);
                    Map entry = new HashMap();
                    if(rowIndex ==179){
                        logger.debug(rowIndex+" rowIndex debug");
                    }
                    if(row == null){
                        continue;
                    }
                    int cellNum = row.getPhysicalNumberOfCells();
                    if (cellNum > 0) {

                        Cell cell = row.getCell(headNums[0]);//教室 非必填

                        Cell cell1 = row.getCell(headNums[1]);//老师 必填
                        Integer teacherid = null;
                        String _tearcher = getCellValue(cell1);
                        if (!StringUtil.isStrEmpty(_tearcher)) {
                            teacherid = _teacherNameid.get(_tearcher);
                            if (teacherid == null){
                                teacherid = AuthUser.dao.saveTeacher(_tearcher);
                                _teacherNameid.put(_tearcher,teacherid);
                            }
                        }
                        Cell cell2 = row.getCell(headNums[2]);//时间 必填

                        List<Date> _time = null;
                        String _times = getCellValue(cell2);
                        if (!StringUtil.isStrEmpty(_times)) {
                            _time = this.getDateByStr(_times,_date);
                        }

                        if (teacherid != null && _time != null && _time.size()>1) {
                            for(int i=3;i<=9;i++) {
                                _date = calendar.getTime();//先获取时间，在加1
                                calendar.add(Calendar.DAY_OF_YEAR,1);
                                Cell cellstudent = row.getCell(headNums[i]);//学生( 星期一 必填 for 到 周日)
                                String _studentStr = getCellValue(cellstudent);
                                //处理各种情况,拆分出来 学生
                                //分隔符：+号
                                //拆分符：回车，—（固定补课 临时加课9.24
                                // 1:30-12:00 /音乐，（音乐 陪读 周末不加班 预留课表

                                if(StringUtil.isStrEmpty(_studentStr)){
                                    continue;
                                }

                                MeetingsMeeting meetingsMeeting = new MeetingsMeeting();
                                meetingsMeeting.setUserId(userid);
                                meetingsMeeting.setDescription(_studentStr);
                                meetingsMeeting.setName(_studentStr);
                                meetingsMeeting.setStartTime(_time.get(0));
                                meetingsMeeting.setEndTime(_time.get(1));
                                meetingsMeeting.setDate(_date);
                                meetingsMeeting.setModifyTime(new Date());
                                meetingsMeeting.setRoomId(_teacherid_roomid.get(teacherid)==null
                                ? 1:_teacherid_roomid.get(teacherid));
                                if(_studentStr.contains("半节")||
                                        _studentStr.contains("半小时")
                                ){
                                    meetingsMeeting.setWeight(0.5);
                                }

//                                    meetingsMeeting.setRemark();
                                meetingsMeeting.save();
                                Integer meetingid = meetingsMeeting.getId();
                                //add meegingatt
                                MeetingsMeetingattendee meetingsMeetingattendee = new MeetingsMeetingattendee();
                                meetingsMeetingattendee.setModifyTime(new Date());
                                meetingsMeetingattendee.setMeetingId(meetingid);
                                meetingsMeetingattendee.setUserId(teacherid);
                                meetingsMeetingattendee.save();

                                //秋季班上半节，秋季班下半节，秋季班全，幼小衔接班14:45-17:00
                                String[] _classtype =new String[] {"秋季班","幼小衔接班"};

                                if(_studentStr.contains(_classtype[0]) ||
                                        _studentStr.contains(_classtype[1])
                                ){
                                    continue;
                                }
                                Log.getLog(this.getClass()).debug("------rowIndex"+rowIndex+"_studentStr"+_studentStr);

                                //处理各种情况，+，括号，回车，备注，时间
                                List<String> _realStus = getRealStu(_studentStr);
                                int classtype = MeetingStatus.classtype_pingge;
                                if(_realStus.size()>1){
                                    classtype = MeetingStatus.classtype_pingtuan;
                                }
                                meetingsMeeting.setType(classtype+"");//自动解析 type,目前就解析 平个和平团
                                meetingsMeeting.update();
                                for(String _relaStu:_realStus){
                                    if(_relaStu.contains("#") && _relaStu.split("#").length>1){
                                        String[] _s = _relaStu.split("#");
                                        String _room = _s[1];
                                        Integer _roomid = 1;
                                        if(_roomNameid.containsKey(_room)){
                                            _roomid = _roomNameid.get(_room);
                                        }else{
                                            MeetingsRoom meetingsRoom = new MeetingsRoom();
                                            meetingsRoom.setName(_room);
                                            meetingsRoom.setCreateUserId(1);
                                            meetingsRoom.setModifyTime(new Date());
                                            meetingsRoom.setDeleteStatus(false);
                                            meetingsRoom.setId(NumberTool.safeToInteger(_room,1));
                                            meetingsRoom.save();
                                            _roomid = meetingsRoom.getId();
                                        }
                                        meetingsMeeting.setRoomId(_roomid);
                                        meetingsMeeting.update();
                                        _relaStu = _s[0];
                                    }
                                    Integer _studentid = _studentNameid.get(_relaStu);
                                    if(_studentid ==null){
                                        _studentid = AuthUser.dao.saveStudent(_relaStu);
                                        _studentNameid.put(_relaStu,_studentid);
                                    }
                                    MeetingsMeetingattendee stu = new MeetingsMeetingattendee();
                                    stu.setModifyTime(new Date());
                                    stu.setMeetingId(meetingid);
                                    stu.setUserId(_studentid);
                                    stu.save();
                                }

                            }
                        }
                    }
                } catch (Exception e) {
                    Log.getLog(this.getClass()).debug("------rowIndex"+rowIndex);
                    e.printStackTrace();
                    break;
                }
            }
            calendar.setTime(beginSave);
            calendar.add(Calendar.DAY_OF_YEAR,7);
            Date _endsave = calendar.getTime();
            for(;nowdate.before( endcopydate);){

                Calendar calendar1 = Calendar.getInstance();
                calendar1.setTime(nowdate);
                int nowDay = calendar1.get(Calendar.DAY_OF_WEEK);
                calendar1.setTime(beginSave);
                calendar1.set(Calendar.DAY_OF_WEEK,nowDay);
                Date _classNowday = calendar1.getTime();
                Log.getLog(this.getClass()).debug(
                        "\n---copyfrom 》_classNowday：" +TimeUtil.formatDate(_classNowday, "yyyy-MM-dd")
                        + "\n---copyto 》nowdate：" + TimeUtil.formatDate(nowdate, "yyyy-MM-dd")
                        + "\n--- 》_date：" + TimeUtil.formatDate(_date, "yyyy-MM-dd")
                );
                if((nowdate.after(beginSave) && nowdate.before(_endsave))
                        || nowdate.equals(beginSave) ||
                        nowdate.equals(_endsave)){
                    Log.getLog(this.getClass()).debug("--- beginsave pass");
                }else {
                    Log.getLog(this.getClass()).debug("--- copy");
                    copyClass(_classNowday,nowdate,type);
                }
                calendar1.setTime(nowdate);
                calendar1.add(Calendar.DAY_OF_YEAR,1);
                nowdate = calendar1.getTime();
            }

        }catch (Exception e){
            Log.getLog(this.getClass()).debug("导入课程异常");
            e.printStackTrace();
            renderJson(Ret.fail().set("message","导入课程异常"));
        }
        renderJson(Ret.ok("code",0).set("message","返回成功"));
    }

    public Map<String,List<Integer>> timelist = getTimeList();

    public List<String> getRealStu(String _str){
        List<String> _re = new ArrayList<>();
        if(StringUtil.isStrEmpty(_str)){
            return _re;
        }
        //分隔符：+号
        //拆分符：回车，—（固定补课 临时加课9.24
        // 1:30-12:00 /音乐，（音乐 陪读 周末不加班 预留课表
        if(_str.startsWith("苏文")){
            Log.getLog("").debug(_str);
        }
        List<String> _split =Arrays.asList( new String[]{"\n","—","（","\\(","固定补课","临时加课"
                ,"/","陪读","周末不加班","预留课表"});
        for(String _s:_split){
            if(_str.contains(_s)){
                String[] _resu = _str.split(_s);
                if(_resu.length == 0){
                    continue;
                }
                _str = _resu[0];
            }
        }
        if(_str.contains("+")){
            _re = Arrays.asList(_str.split("\\+"));
        }else if (!StringUtil.isStrEmpty(_str)){
            _re = Arrays.asList(new String[]{_str});
        }
        //这里不需要特殊处理，最后一个人带#号，即可，列表的人，均为一节课的
        /*if(_str.contains("#") && _str.split("#").length>1){
            String _room = _str.split("#")[1];
            List<String> _ls = new ArrayList<>();
            for(String s:_re){
                s+="#"+_room;
                _ls.add(s);
            }
            _re = _ls;
        }*/
        return _re;
    }
    public Map<String,Integer> initUser(){//"学生" "老师"
        List<AuthUser> all = AuthUser.dao.findAll();
        for(AuthUser authUser:all){
            if("学生".equals(authUser.getRole())){
                _studentNameid.put(authUser.getFirstName(),authUser.getId());
            }else if("老师".equals(authUser.getRole())){
                _teacherNameid.put(authUser.getFirstName(),authUser.getId());
                _teacherid_roomid.put(authUser.getId(),authUser.getDefaultRoomid());
            }
        }

        List<MeetingsRoom> allroom = MeetingsRoom.dao.findAll();
        for(MeetingsRoom meetingsRoom:allroom){
            _roomNameid.put(meetingsRoom.getName(),meetingsRoom.getId());
        }
        return null;
    }

    public Map<String,List<Integer>> getTimeList(){
        Map<String,List<Integer>> _result = new HashMap<>();
        String [] _keys = new String[]{
                "am9-10"
                ,"am10-11"
                ,"am11-12"
                ,"pm1-2"
                ,"pm2-3"
                ,"pm3-4"
                ,"pm4-5"
                ,"pm5-6"};
        List<String> list = Arrays.asList(_keys);
        for(String _key:list){
            Integer _hour = 0;
            if(_key.startsWith("pm")){
                _hour = 12;
            }
            String _t = _key.substring(2);
            String[] _ts = _t.split("-");
            Integer begin = _hour + Integer.valueOf(_ts[0]);
            Integer end = _hour + Integer.valueOf(_ts[1]);
            List<Integer> list1 = new ArrayList<>();
            list1.add(begin);
            list1.add(end);
            _result.put(_key,list1);
        }
        return _result;
    }

    private List<Date> getDateByStr(String key,Date date){
        List<Date> result = new ArrayList<Date>();
        List<Integer> list = timelist.get(key);
        if(list == null || list.size()<2){
            return result;
        }
        Calendar calendar = Calendar.getInstance();
        Date _usedate = TimeUtil.formatDate(
                TimeUtil.formatDate(date,"yyyy-MM-dd"),"yyyy-MM-dd");
        calendar.setTime(_usedate);
        calendar.add(Calendar.HOUR_OF_DAY,list.get(0));
        result.add(calendar.getTime());

        calendar.setTime(_usedate);
        calendar.add(Calendar.HOUR_OF_DAY,list.get(1));
        result.add(calendar.getTime());
        return result;
    }

    /**
     * 获取表头对应的列数
     * @param headname
     * @return
     */
    public static Integer[] getHeadNums(String[] headname,Sheet sheet){
        Integer[] headNums=new Integer[headname.length];
        Row row = sheet.getRow(0);//获取表头
        String str="";
        int count = 0;
        for (int i = 0; i < headname.length; i++) {
            for (int rnum=0; rnum < 20; rnum++) {// 只判断前 20列
                str = getCellValue(row.getCell(rnum));
                if (StringUtil.isStrEmpty(str)) {
                    break;
                }
                if (str.trim().equals(headname[i])) {
                    count++;
                    headNums[i]=rnum;
                    break;
                }
            }
        }
        if(count == headname.length){
            return headNums;
        }else {
            return null;
        }
    }

    public static Workbook parse(FileInputStream buffer, String suffix) {
        Workbook text = null;
        switch (suffix) {
            case "xls":
                text = getTextFromExcel(buffer);
                break;
            case "xlsx":
                text = getTextFromExcel2007(buffer);
                break;
        }
        return text;
    }

    // 读取Excel97-2003的全部内容 xls
    private static Workbook getTextFromExcel(FileInputStream file) {
        Workbook wb = null;
        String text = "";
        try {
            wb = new HSSFWorkbook(new POIFSFileSystem(file));
//            ExcelExtractor extractor = new ExcelExtractor(wb);
//            extractor.setFormulasNotResults(false);
//            extractor.setIncludeSheetNames(false);
//            text = extractor.getText();
//            extractor.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

    // 读取Excel2007+的全部内容 xlsx
    private static Workbook getTextFromExcel2007(FileInputStream file) {
        Workbook workBook = null;
        String text = "";
        try {
            workBook = new XSSFWorkbook(file);
//            XSSFExcelExtractor extractor = new XSSFExcelExtractor(workBook);
//            extractor.setIncludeSheetNames(false);
//            text = extractor.getText();
//            extractor.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return workBook;
    }

    public static String getCellValue(Cell cell){
        NumberFormat formater = NumberFormat.getInstance();
        formater.setGroupingUsed(false);
        String cellValue="";
        if(cell==null){
            return cellValue;
        }
        switch(cell.getCellType()){
            case Cell.CELL_TYPE_NUMERIC:
                java.math.BigDecimal big = new java.math.BigDecimal(cell.getNumericCellValue());
                cellValue = StringUtil.safeToString(big.toString(), "");
                break;
            case Cell.CELL_TYPE_FORMULA:
                try {
                    cellValue = cell.getStringCellValue();
                } catch (IllegalStateException e) {
                    java.math.BigDecimal big1 = new java.math.BigDecimal(cell.getNumericCellValue());
                    cellValue = StringUtil.safeToString(big1.toString(), "");
                }
                break;
            case HSSFCell.CELL_TYPE_STRING:
                cellValue = cell.getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_BLANK:
                cellValue = cell.getStringCellValue();
                break;
            case HSSFCell.CELL_TYPE_BOOLEAN:
                cellValue = Boolean.valueOf(cell.getBooleanCellValue()).toString();
                break;
            case HSSFCell.CELL_TYPE_ERROR:
                cellValue = String.valueOf(cell.getErrorCellValue());
                break;
            default:cellValue="";
        }
        return cellValue.trim();
    }


}
